"""
Case Type   : 服务端工具
Case Name   : 备份结果包含依赖于指定对象的对象信息（--include-depend-objs）
Create At   : 2021/2/7
Owner       : opentestcase002
Description :
    1.创建数据库
    2.创建模式，创建表并插入数据，设置表的所属模式
    3.创建表文件
    4.备份结果包含依赖于指定对象的对象信息（--include-depend-objs）
    5.连接数据库，清理环境
Expect      :
    1.创建数据库成功
    2.创建模式成功,创建表并插入数据成功
    3.创建表文件
    4.导出成功
    5.清理环境成功
History     :
    modify by opentestcase002 20220811:研发代码变更，优化断言
    modify by opentestcase002 20221130:研发代码变更，优化断言
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('dbuser')
        self.db_name = 'db_dump_case0066'
        self.t_name1 = 't_dump_case0066_01'
        self.t_name2 = 't_dump_case0066_02'
        self.t_name3 = 't_dump_case0066_03'
        self.v_name = 'v_dump_case0066'
        self.i_name = 'i_dump_case0066'
        self.sch_name1 = 'sch_dump_case0066_01'
        self.sch_name2 = 'sch_dump_case0066_02'
        self.sch_name3 = 'sch_dump_case0066_03'
        self.constant = Constant()
        self.path = os.path.join(macro.DB_INSTANCE_PATH, 'dump_config')

    def test_server_tools(self):
        text = '-----step1:创建数据库,expect:成功-----'
        self.log.info(text)
        sql_cmd1 = f'''drop database if exists {self.db_name};
            create database {self.db_name};
                   '''
        msg1 = self.sh_primy.execut_db_sql(sql_cmd1)
        self.log.info(msg1)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg1,
                      '执行失败:' + text)

        text = '-----step2:在创建好的数据库中创建模式，创建表\
        并插入数据并设置表的所属模式,expect:成功-----'
        self.log.info(text)
        sql_cmd2 = f''' drop schema if exists {self.sch_name1};
            drop schema if exists {self.sch_name2};
            drop schema if exists {self.sch_name3};
            create schema  {self.sch_name1};
            create schema  {self.sch_name2};
            create schema  {self.sch_name3};
            drop table if  exists {self.t_name1};
            drop table if  exists {self.t_name2};
            drop table if  exists {self.t_name3};
            create table {self.t_name1} (id int);
            insert into {self.t_name1} values(1),(2),(3);
            create table {self.t_name2} (id int);
            insert into {self.t_name2} values(8),(2),(5);
            create table {self.t_name3} (id int);
            insert into {self.t_name3} values(9),(6),(3);
            alter table {self.t_name1} set schema {self.sch_name1};
            alter table {self.t_name2} set schema {self.sch_name2};
            alter table {self.t_name3} set schema {self.sch_name3};
            drop view if exists view_{self.t_name1};
            create view {self.v_name} as select * from 
            {self.sch_name1}.{self.t_name1};
            drop index if exists {self.i_name} cascade;
            create unique index {self.i_name} on 
            {self.sch_name2}.{self.t_name2} (id);
            '''
        msg2 = self.sh_primy.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.log.info(msg2)
        self.assertEqual(3, msg2.count('CREATE SCHEMA'), '执行失败:' + text)
        self.assertEqual(3, msg2.count('CREATE TABLE'), '执行失败:' + text)
        self.assertEqual(3, msg2.count('INSERT'), '执行失败:' + text)
        self.assertEqual(3, msg2.count('ALTER TABLE'), '执行失败:' + text)
        self.assertEqual(1, msg2.count('CREATE VIEW'), '执行失败:' + text)
        self.assertEqual(1, msg2.count('CREATE INDEX'), '执行失败:' + text)

        text = '-----step3:创建表文件,expect:成功-----'
        self.log.info(text)
        shell_cmd = f'''echo '{self.sch_name1}.{self.t_name1}' >> {self.path};
            echo '{self.sch_name2}.{self.t_name2}' >> {self.path};
            echo '{self.sch_name3}.{self.t_name3}' >> {self.path};
            '''
        self.log.info(shell_cmd)
        shell_msg = self.dbuser_node.sh(shell_cmd).result()
        self.log.info(shell_msg)
        cat_cmd = f'cat {self.path};'
        self.log.info(cat_cmd)
        cat_msg = self.dbuser_node.sh(cat_cmd).result()
        self.log.info(cat_msg)
        self.assertIn(f'{self.sch_name1}.{self.t_name1}', cat_msg,
                      '执行失败:' + text)
        self.assertIn(f'{self.sch_name2}.{self.t_name2}', cat_msg,
                      '执行失败:' + text)
        self.assertIn(f'{self.sch_name3}.{self.t_name3}', cat_msg,
                      '执行失败:' + text)

        text = '-----step4:导出备份结果包含依赖于指定对象的对象信息,expect:成功-----'
        self.log.info(text)
        excute_cmd3 = f'source {macro.DB_ENV_PATH} ;' \
            f'gs_dump ' \
            f'-p {self.dbuser_node.db_port} {self.db_name}  ' \
            f'-F p  ' \
            f'--include-depend-objs  ' \
            f'--include-table-file={self.path};'
        self.log.info(excute_cmd3)
        msg3 = self.dbuser_node.sh(excute_cmd3).result()
        self.log.info(msg3)
        self.assertIn(f'CREATE TABLE {self.t_name1}', msg3, '执行失败:' + text)
        self.assertIn(f'CREATE TABLE {self.t_name2}', msg3, '执行失败:' + text)
        self.assertIn(f'COPY {self.sch_name1}.{self.t_name1}', msg3,
                      '执行失败:' + text)
        self.assertIn(f'COPY {self.sch_name2}.{self.t_name2}', msg3,
                      '执行失败:' + text)
        self.assertIn(f'CREATE UNIQUE INDEX {self.i_name} ON {self.t_name2}',
                      msg3, '执行失败:' + text)

    def tearDown(self):
        text = '-----step5:清理环境,expect:成功-----'
        self.log.info(text)
        sql_cmd4 = f'''drop database if exists {self.db_name}; '''
        msg4 = self.sh_primy.execut_db_sql(sql_cmd4)
        self.log.info(msg4)
        rm_cmd = f"rm -rf {self.path};"
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertEqual(len(rm_msg), 0, '执行失败:' + text)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, msg4,
                      '执行失败:' + text)
        text = f'-----{os.path.basename(__file__)} end-----'
        self.log.info(text)
